package com.lemurian.bankdata.bean;

import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import com.lemurian.bankdata.bean.base.BaseDaoHibernate;
import com.lemurian.bankdata.lite.logger.BankDataLogger;
import com.lemurian.bankdata.lite.struts.PartialList;
import com.lemurian.bankdata.report.dto.RekapSertifikat;
import com.lemurian.entity.Management;

@Repository
public class ManagementDAO extends BaseDaoHibernate<Management>{
	
	private BankDataLogger log = BankDataLogger.getLogger(ManagementDAO.class.getName());

	public PartialList<Management> delegasiKurirList(int start, int count, Order order, Criterion... criterion) {
		PartialList<Management> partialList = null;
		
		try {
			log.info("Get in");
			// total
			Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Management.class);
			
			criteria.createAlias("client", "client", Criteria.LEFT_JOIN);
			criteria.createAlias("client.debitur", "debitur", Criteria.LEFT_JOIN);
			criteria.createAlias("courier", "courier", Criteria.LEFT_JOIN);
			criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
			
			
			criteria.setCacheable(true);
			for (Criterion c : criterion) {
				criteria.add(c);
			}
			criteria.setProjection(Projections.rowCount());
			int total = ((Integer) criteria.list().iterator().next()).intValue();
			// partial data
			criteria = sessionFactory.getCurrentSession().createCriteria(Management.class);
			
			criteria.createAlias("client", "client", Criteria.LEFT_JOIN);
			criteria.createAlias("client.debitur", "debitur", Criteria.LEFT_JOIN);
			criteria.createAlias("courier", "courier", Criteria.LEFT_JOIN);
			criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
			
			criteria.setCacheable(true);
			for (Criterion c : criterion) {
				criteria.add(c);
			}
			if (order!=null) criteria.addOrder(order);
			criteria.setFirstResult(start);
			criteria.setMaxResults(count);
			
			partialList = new PartialList<Management>(criteria.list(), total);
		} catch(Exception exception){
			exception.printStackTrace();
			//sessionFactory.getCurrentSession().getTransaction().rollback();
		}
		
		return partialList;
	}
	
	@Override
	public List<Management> findByCriteria(Order order, Criterion... criterion) {
		try {
			log.info("Get Overide");
			Criteria criteria = getSession().createCriteria(Management.class);
			criteria.createCriteria("bpnRegion", "bpnRegion", Criteria.LEFT_JOIN);
			criteria.createCriteria("occasionsCode", "occasionsCode", Criteria.LEFT_JOIN);
			criteria.createCriteria("client", "client", Criteria.LEFT_JOIN);
			criteria.createCriteria("client.debitur", "debitur", Criteria.LEFT_JOIN);
			criteria.createCriteria("managementType", "managementType", Criteria.LEFT_JOIN);
			criteria.createCriteria("managementType.parent", "department", Criteria.LEFT_JOIN);
			criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
			
			criteria.setCacheable(true);
			for (Criterion c : criterion) {
				criteria.add(c);
			}
			if (order!=null) criteria.addOrder(order);
			List<Management> list = criteria.list();
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	public Management findByIdJoinManagementType(int id) {
		try {
			log.info("Get Overide");
			Criteria criteria = getSession().createCriteria(Management.class);
			criteria.createCriteria("managementType", "managementType");
			criteria.createCriteria("managementType.parent", "department");
			criteria.add(Restrictions.eq("id", id));
			criteria.setCacheable(true);
			
			return (Management) criteria.uniqueResult();
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}
	
	@SuppressWarnings("unchecked")
	public List<RekapSertifikat> summaryReport(Date startTime, Date endTime, Boolean status) {
		List<RekapSertifikat> list = null;
		
		try {
			Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Management.class);
			
			criteria.createCriteria("occasionsCode", "occasionsCode");
			criteria.createCriteria("client", "client");
			criteria.createCriteria("client.signatureSchedules", "signatureSchedules");
			
			criteria.setProjection(initializeProjection());
			
			criteria.add(Restrictions.eq("occasionsCode.parent.id", 18));
			
			if(status) {
				criteria.add(Restrictions.eq("signatureSchedules.signatureStatus", status));
			} else {
				criteria.add(Restrictions.eq("signatureSchedules.signatureStatus", status));
			}
			
			Criterion criterion = null;
			if(startTime == null && endTime !=null) {
                criterion = Restrictions.le("endTime", endTime);
            } else if(startTime != null && endTime == null) {
                criterion = Restrictions.ge("startTime", startTime);
            } else if(startTime != null && endTime !=null) {
                criterion = Restrictions.and(Restrictions.ge("startTime", endTime), Restrictions.le("endTime", endTime));
            } else {
                criterion = Restrictions.ne("id", new Integer(-1));
            }
			
			criteria.add(criterion);
			
			criteria.setResultTransformer(Transformers.aliasToBean(RekapSertifikat.class));
			
			list = criteria.list();
		} catch (Throwable t) {
			t.printStackTrace();
		}
		
		return list;
	}
	
	private ProjectionList initializeProjection() {
        ProjectionList projections = Projections.projectionList();

        projections.add(Projections.property("occasionsCode.name").as("kodeUrusan"));
        projections.add(Projections.count("occasionsCode.name").as("jumlah"));

        projections.add(Projections.groupProperty("occasionsCode.name"));
        
        return projections;
    }
}